import openpyxl

wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp\1.xlsx")
ws = wb.worksheets[0]
ws2 = wb.worksheets[1]

m_start = 1
m_end = 12

dict_sn = {}
temp = 2
for i in range(2, ws.max_row + 1):
    if ws.cell(i, 4).value not in dict_sn:
        ws2.cell(temp, 1).value = ws.cell(i, 4).value
        dict_sn[ws.cell(i, 4).value] = temp
        temp += 1
    else:
        pass

for i in range(2, ws2.max_row + 1):
    for j in range(2, m_end * 3 + 2):
        if ws2.cell(i, j).value is None:
            ws2.cell(i, j).value = 0

for i in range(2, ws.max_row + 1):
    if m_start <= int(ws.cell(i, 5).value[5:7]) <= m_end:
        if ws.cell(i, 15).value == "借出归还单":
            ws2.cell(dict_sn[ws.cell(i, 4).value], int(ws.cell(i, 5).value[5:7]) * 3 + 1).value += ws.cell(i, 8).value
        if ws.cell(i, 15).value == "销货单":
            ws2.cell(dict_sn[ws.cell(i, 4).value], int(ws.cell(i, 5).value[5:7]) * 3 - 1).value += ws.cell(i, 8).value
        if ws.cell(i, 15).value == "借出单":
            ws2.cell(dict_sn[ws.cell(i, 4).value], int(ws.cell(i, 5).value[5:7]) * 3).value += ws.cell(i, 8).value

list1 = list(range(m_start * 3 - 1, m_end * 3, 3))
list2 = list(range(m_start * 3, m_end * 3 + 1, 3))
list3 = list(range(m_start * 3 + 1, m_end * 3 + 2, 3))

for i in range(2, ws2.max_row + 1):
    temp = 0
    for j in list1:
        temp += ws2.cell(i, j).value
    for j in list2:
        temp += ws2.cell(i, j).value
    for j in list3:
        temp -= ws2.cell(i, j).value
    ws2.cell(i, m_end * 3 + 2).value = temp / m_end - m_start + 1

ws2.cell(1, 1).value = "规格"
ws2.column_dimensions['A'].width = 25

for i in range(m_start, m_end + 1):
    ws2.cell(1, i * 3 - 1).value = str(i) + "月销货"
    ws2.cell(1, i * 3).value = str(i) + "月借出"
    ws2.cell(1, i * 3 + 1).value = str(i) + "月借出归还"
ws2.cell(1, m_end * 3 + 2).value = "月平均提货量"
wb.save(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp\2.xlsx")
